<?php
class Directory_services_model extends MY_Model{
    function Directory_services_model(){
        parent::MY_Model();
        $this->directory_services_table = "directory_services";
        $this->directory_services_relation_table = "directory_services_relationship";
        $this->directory_province_relation_table = "directory_province_relationships";
    }
    
    function get_list_services_with_usage($category = "products"){
        $sql = "SELECT  * FROM directory_services as ser";
        $sql .= " LEFT JOIN  ";
        $sql .= "( SELECT count(id_company) as jumlah, id_services FROM directory_services_relationship GROUP BY id_services) as rel ";
        $sql .= " ON ser.id = rel.id_services ";
        $sql .= " WHERE ser.category = '{$category}' ORDER BY ser.service_type ASC,ser.name ASC";
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            return $result;
        } else {
            return false;
        }        
    }
    
    function get_list_services($category = "products"){
        $this->db->order_by('service_type');
        $query = $this->db->get_where($this->directory_services_table, array('category' => $category));
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            return $result;
        } else {
            return false;
        }    
    }
    function get_list_province($directory_user_id){
         $sql = "SELECT  * FROM ".$this->directory_province_relation_table." as dpr";
        $sql .= " JOIN  region as r on r.id=dpr.province_id";
        $sql .= " where dpr.directory_user_id=$directory_user_id";
        $query = $this->db->query($sql);
       if ($query->num_rows() > 0) {
            $result = $query->result_array();
            return $result;
        } else {
            return false;
        }
    }
    
    function add_services($category = '', $service_type = '', $services_name = ''){
        $data = array(
           'category' => $category ,
           'service_type' =>  $service_type ,
           'name' => $services_name ,
        );
        $this->db->insert($this->directory_services_table, $data);
        return $this->db->last_query();    
    }
    
    function update_services($category = '', $service_type ='', $services_name = '', $services_id = ''){
        $data = array(
           'category' => $category ,
           'service_type' =>  $service_type ,
           'name' => $services_name ,
        );
        $this->db->where('id', $services_id);
        $this->db->update($this->directory_services_table, $data);        
        return $this->db->last_query();            
    }
    
    function delete_services($services_id = ''){  
        $this->db->delete($this->directory_services_table, array('id' => $services_id));         
        return $this->db->last_query();            
    }
    function remove_service_category_relation($id_company,$category_id){
        $status = $this->db->delete($this->directory_services_relation_table, array('id_company' => $id_company, 'id_services' =>  $category_id));
        return $status;
    }

    function add_directory_service_relation($id_company = '', $category_id = ''){
        $data = array(
           'id_company' => $id_company ,
           'id_services' =>  $category_id ,
        );
        $this->db->insert($this->directory_services_relation_table, $data);
        return $this->db->last_query();
    }
    function remove_directory_province_relation($directory_user_id,$province_id){
        $status = $this->db->delete($this->directory_province_relation_table, array('directory_user_id' => $directory_user_id, 'province_id' =>  $province_id));
        return $status;
    }

    function add_directory_province_relation($directory_user_id = '', $province_id = ''){
        $data = array(
           'directory_user_id' => $directory_user_id ,
           'province_id' =>  $province_id ,
        );
        $this->db->insert($this->directory_province_relation_table, $data);
        return $this->db->last_query();
    }
}
?>
